Tai mais um módulo que você tem que guardar no seu cinto de utilidades. As funções e subs abaixo acessam o Microsft SQL Server fazendo tudo que é possível nele, da conexão ao retorno de dados.
Para acessar o SQL Server você precisa adicinar uma ferramenta opcional no visual studio de acesso ao MS SQL Server. Faça o seguinte:
• 1 - Clique no menu Ferramentas.
• 2 - Selecione a opção 'Gerenciador de pacotes do Nugget'
• 3 - Na caixa pesquisar digite 'system.data.sqlclient'
Instale o pacote.
Imports System.Data.SqlClient
Module ModuloMSSQL
Public cnstr As String = "" 'string de conexão com o banco de dados
Public cncn As New SqlConnection 'conexão com o banco de dados
Public Erro As String = "" 'troca de mensagens de erro
''' <summary>
''' Define o string de conexão com o banco de dados que dependente da url de acesso do site
''' </summary>
Public Sub DefineStrConexaoMSSQL()
Dim txtServidor As String = "ip ou nome servidor"
Dim txtBD As String = "usuario_acesso_mssql"
Dim txtUsuario As String = "_usuario_mssql"
Dim txtSenha As String = "senha_usuario_mssql"
cnstr = "Server=" + txtServidor + ";Database=" + txtBD + ";User Id=" + txtUsuario + ";Password=" + txtSenha
End Sub
''' <summary>
''' abre a conexão com o banco de dados
''' </summary>
Public Sub AbreConexaoMSSQL()
Erro = ""
Try
If cncn.ConnectionString = "" Then
DefineStrConexaoMSSQL()
cncn.ConnectionString = cnstr
cncn.Open()
Return
End If
Catch ex As SqlException
Erro = ex.Message
End Try
End Sub
''' <summary>
''' Faz a pesquisa e retorna um DataSet
''' </summary>
Public Function MS_SQL_SRV_DS(Query As String) As DataSet
Erro = ""
Try
Dim cmd = New SqlCommand(Query, cncn)
Dim MS_SQL_SqlDtAdapter = New SqlDataAdapter(cmd)
Dim DS As New DataSet()
MS_SQL_SqlDtAdapter.Fill(DS)
Return DS
Catch ex As Exception
Erro = ex.Message
Return Nothing
End Try
End Function
''' <summary>
'''executa um sql que não retorna nada
''' </summary>
Public Sub MS_SQL_SRV_EX(query As String)
Erro = ""
Try
Dim cmd = New SqlCommand(query, cncn)
cmd.CommandTimeout = 20000
cmd.ExecuteNonQuery()
'cncn.Close()
Catch ex As Exception
Erro = ex.Message
'Throw ex
End Try
End Sub
''' <summary>
'''executa um sql que retorna um inteiro
''' </summary>
Public Function MS_SQL_SRV_INT(query As String) As Integer
Erro = ""
Try
Dim cmd = New SqlCommand(query, cncn)
cmd.CommandText = query
Return cmd.ExecuteScalar()
Catch ex As Exception
Return Nothing 'erro
Erro = ex.Message
'Throw ex
End Try
End Function
''' <summary>
''' executa um sql que retorna um String
''' </summary>
''' <param name="query"></param>
''' <returns></returns>
Public Function MS_SQL_SRV_STR(query As String) As String
Erro = ""
Try
Dim cmd = New SqlCommand(query, cncn)
cmd.CommandText = query
Return cmd.ExecuteScalar()
Catch ex As Exception
Erro = ex.Message
Return Nothing
'Throw ex
End Try
End Function
''' <summary>
''' executa um sql que retorna um single(sql)
''' </summary>
''' <param name="query"></param>
''' <returns></returns>
Public Function MS_SQL_SRV_Single(query As String)
Erro = ""
Try
Dim cmd = New SqlCommand(query, cncn)
cmd.CommandText = query
Return cmd.ExecuteScalar()
Catch ex As Exception
Erro = ex.Message
Return Nothing 'erro
'Throw ex
End Try
End Function
''' <summary>
'''faz a pesquisa e retorna um RecordSet
''' </summary>
Public Function MS_SQL_RS(query As String) As DataSet
Erro = ""
Try
Dim da As New SqlDataAdapter(query, cncn)
Dim ds As New DataSet
da.Fill(ds)
'cncn.Close()
Return ds
Catch ex As SqlException
Erro = ex.Message
Return Nothing
'Throw ex
End Try
End Function
''' <summary>
''' Executa uma procedure e retorna um dataset
''' Parametros = "P1,P2,P3,P4..."
''' </summary>
Public Function MS_SQL_SQL_Proc(Procedure As String, Parametros As String) As DataSet
Erro = ""
Try
Dim SQL As String = Procedure + " " + Parametros
Dim cmd As New SqlCommand(SQL, cncn)
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)
Return ds
Catch ex As Exception
Erro = ex.Message
Return Nothing
'Throw ex
End Try
End Function
Public Sub SalvaProcMSSQL(NomeProc As String)
Dim sql As String
Dim ds As DataSet
Dim NomeArq As String
Dim a As Integer
Dim b As String
sql = "use txtBD"
ds = MS_SQL_SRV_DS(sql)
'pegando o nome das procs no bd
sql = "sp_helptext " + NomeProc
ds = MS_SQL_SRV_DS(sql)
If Erro <> "" Then
MsgBox("Ocorreu um erro:" + Erro)
Exit Sub
End If
If ds.Tables.Count <= 0 Then
Exit Sub
End If
If ds.Tables(0).Rows.Count = 0 Then
'MsgBox("x")
Exit Sub
End If
b = ""
For a = 0 To ds.Tables(0).Rows.Count - 1
b += ds.Tables(0).Rows(a).Item(0)
Next
'b = EliminarLinhasemBranco(b)
Dim file As System.IO.StreamWriter
NomeArq = NomeProc + ".sql"
file = My.Computer.FileSystem.OpenTextFileWriter(NomeArq, True)
file.Write(b)
file.Close()
Application.DoEvents()
End Sub
End Module
A function AbreConexaoMSSQL abre a conexão com o MS SQL Server. Altere os parâmetros para acessar o seu servidor. Note que a autenticação utilizada é via sql server. Caso você use um banco de dados local use o parâmetro TrustedConnection.
A function MS_SQL_SRV_DS recebe como parâmetro um SQL e retorna um dataset.
O padrão de uso é:
Public Function UsuarioExiste(id As String) As Boolean
Dim sql As String
Dim ds As DataSet
AbreConexaoMSSQL()
sql = "select * from Usuario where id='" + id + "'"
ds = MS_SQL_SRV_DS(sql)
If Erro <> "" Then
msg("ERRO : SQL retornou erro na função UsuarioExiste")
Return Nothing
End If
If IsNothing(ds) Then
Return False
End If
If ds.Tables(0).Rows.Count > 0 Then
Return True
End If
Return False
End Function
Portanto a rotina ou retorna um Dataset ou a variável de erro setada.
A função MS_SQL_SRV_EX executa um proc que não retorna dados como é o caso do insert ou update. Ela recebe como parâmetro o sql e não retorna nada. Apenas a variável erro virá setada em caso de erro.
As funções MS_SQL_SRV_INT, MS_SQL_SRV_STR, MS_SQL_SRV_Single são funções que executam SQL que retornam dados escalares, ou seja, não é um recorset mas sim um dado Único. A primeira retorna um Integer, a Segunda um String e a terceira uma variável numérica tipo single.
A função MS_SQL_RS recebe o parâmetro a pesquisa sql e retorna um recordset.